package com.gmrz.uaf.db.dao;


import com.gmrz.uaf.common.Constants;
import com.gmrz.uaf.db.DAOException;
import com.gmrz.uaf.protocol.v1.processor.exception.UAFErrorCode;
import com.gmrz.uaf.protocol.v1.schema.DeviceDetails;
import com.gmrz.util.Strings;
import com.gmrz.util.UUIDGenerator;
import com.gmrz.util.db.DBUtil;
import com.google.inject.assistedinject.Assisted;
import com.google.inject.assistedinject.AssistedInject;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
public class BaseDevicesDAO implements DevicesDao
{
  private static final Logger LOG = LogManager.getLogger(BaseDevicesDAO.class);
  private Connection conn_;

  private static final String _SQL_INSERT = "insert into t_devices (dev_id,user_name,tenant_id,auth_type,trans_type," +
          "device_id,device_name, alias_name,device_type, os_type,os_version,imei,mac,status,createts, updatets,is_root) " +
          "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

  private static final String _SQL_DEL = "update t_devices set status=? where user_name=? and tenant_id=? and " +
          "device_id=? and auth_type=? and trans_type=?";

  private static final String _SQL_LIST = "select dev_id,user_name,tenant_id,auth_type,trans_type,device_id,device_name,alias_name," +
          " device_type,os_type,os_version,imei,mac,status,createts,updatets " +
          "from t_devices where  tenant_id=? and user_name=? and status=?";

  @AssistedInject
  public BaseDevicesDAO(@Assisted Connection conn_) {
    this.conn_ = conn_;
  }

  public BaseDevicesDAO() {

  }


  /**
     * 查询指定用户在指定应用下的所有有效设备注册信息
     * @param userName
     * @param tenantID
     * @return
     * @throws DAOException
     */
  public List<DeviceDetails> forUserNameAndAppID(String userName,String tenantID)
    throws DAOException
  {
    PreparedStatement ps = null;
    ResultSet rs = null;
    int offset = 1;
    List<DeviceDetails> list = new ArrayList<DeviceDetails>();
    try {
      ps = this.conn_.prepareStatement(_SQL_LIST);
      ps.setString(offset++, tenantID);
      ps.setString(offset++, userName);
      ps.setInt(offset++,Constants.AuthenticatorStatus.ACTIVE.getStatus());
      rs = ps.executeQuery();
        while (rs.next()) {
            DeviceDetails deviceDetail = new DeviceDetails();
            deviceDetail.setId(Strings.escapeHtmlAndScript(rs.getString("dev_id")));
            deviceDetail.setUserName(Strings.escapeHtmlAndScript(rs.getString("user_name")));
            deviceDetail.setAppID(Strings.escapeHtmlAndScript(rs.getString("tenant_id")));
            deviceDetail.setAuthType(Strings.escapeHtmlAndScript(rs.getString("auth_type")));
            deviceDetail.setTransType(Strings.escapeHtmlAndScript(rs.getString("trans_type")));
            deviceDetail.setDeviceID(Strings.escapeHtmlAndScript(rs.getString("device_id")));
            deviceDetail.setDeviceName(Strings.escapeHtmlAndScript(rs.getString("device_name")));
            deviceDetail.setDeviceAliasName(Strings.escapeHtmlAndScript(rs.getString("alias_name")));
            deviceDetail.setDeviceType(Strings.escapeHtmlAndScript(rs.getString("device_type")));
            deviceDetail.setOsType(Strings.escapeHtmlAndScript(rs.getString("os_type")));
            deviceDetail.setOsVersion(Strings.escapeHtmlAndScript(rs.getString("os_version")));
            deviceDetail.setImei(Strings.escapeHtmlAndScript(rs.getString("imei")));
            deviceDetail.setMac(Strings.escapeHtmlAndScript(rs.getString("mac")));
            deviceDetail.setStatus(Integer.parseInt(Strings.escapeHtmlAndScript(String.valueOf(rs.getInt("status")))));
            deviceDetail.setCreateTimeStamp(new Timestamp(Long.parseLong(Strings.escapeHtmlAndScript(String.valueOf(rs.getTimestamp("createts").getTime())))));
            deviceDetail.setUpdateTimeStamp(new Timestamp(Long.parseLong(Strings.escapeHtmlAndScript(String.valueOf(rs.getTimestamp("updatets").getTime())))));
            list.add(deviceDetail);
      }
      return list;
    } catch (SQLException exp) {
        LOG.error("The following SQL statement failed to execute :\n {}", _SQL_LIST);
        LOG.error(exp);
        throw new DAOException(UAFErrorCode.DB_QUERY_DEVICE_LIST_FAILED,exp);
    } finally {
      DBUtil.close(rs);
      DBUtil.close(ps);
    }
  }

  public void delete(String userName, String appID, String deviceID, String authType, String transType) throws DAOException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    int offset = 1;
    try {
      ps = this.conn_.prepareStatement(_SQL_DEL);
      ps.setInt(offset++,Constants.AuthenticatorStatus.DELETED.getStatus());
      ps.setString(offset++, userName);
      ps.setString(offset++, appID);
      ps.setString(offset++, deviceID);
      ps.setString(offset++, authType);
      ps.setString(offset++, transType);
      ps.executeUpdate();
    } catch (SQLException exp) {
      LOG.error("The following SQL statement failed to execute :\n {}", _SQL_DEL);
        LOG.error(exp);
      throw new DAOException(UAFErrorCode.DB_DEL_DEVICE_FAILED,exp);
    } finally {
        DBUtil.close(rs);
        DBUtil.close(ps);
    }
  }

  private DeviceDetails readDeviceDetails(ResultSet rs) throws SQLException {
    DeviceDetails deviceDetail = new DeviceDetails();
    int offset = 1;

    deviceDetail.setId(Strings.escapeHtmlAndScript(rs.getString(offset++)));
    deviceDetail.setUserName(Strings.escapeHtmlAndScript(rs.getString(offset++)));
    deviceDetail.setAppID(Strings.escapeHtmlAndScript(rs.getString(offset++)));
    deviceDetail.setAuthType(Strings.escapeHtmlAndScript(rs.getString(offset++)));
    deviceDetail.setTransType(Strings.escapeHtmlAndScript(rs.getString(offset++)));
    deviceDetail.setDeviceID(Strings.escapeHtmlAndScript(rs.getString(offset++)));
    deviceDetail.setDeviceName(Strings.escapeHtmlAndScript(rs.getString(offset++)));
    deviceDetail.setDeviceAliasName(Strings.escapeHtmlAndScript(rs.getString(offset++)));
    deviceDetail.setDeviceType(Strings.escapeHtmlAndScript(rs.getString(offset++)));
    deviceDetail.setOsType(Strings.escapeHtmlAndScript(rs.getString(offset++)));
    deviceDetail.setOsVersion(Strings.escapeHtmlAndScript(rs.getString(offset++)));
    deviceDetail.setImei(Strings.escapeHtmlAndScript(rs.getString(offset++)));
    deviceDetail.setMac(Strings.escapeHtmlAndScript(rs.getString(offset++)));
    deviceDetail.setStatus(rs.getInt(offset++));
    deviceDetail.setCreateTimeStamp(rs.getTimestamp(offset++));
    deviceDetail.setUpdateTimeStamp(rs.getTimestamp(offset++));
    return deviceDetail;
  }

    private static final String SQL_FIND_ONE = "select dev_id,user_name,tenant_id,auth_type,trans_type,device_id,device_name,alias_name," +
            " device_type,os_type,os_version,imei,mac,status,createts,updatets " +
            "from t_devices where tenant_id=? and user_name=? and device_id=? and auth_type=? and trans_type=?";

    @Override
    public DeviceDetails findOne(String appID, String userName, String deviceID, String authType, String transType) throws DAOException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        int offset = 1;
        try {
            ps = this.conn_.prepareStatement(SQL_FIND_ONE);
            ps.setString(offset++, appID);
            ps.setString(offset++, userName);
            ps.setString(offset++,deviceID);
            ps.setString(offset++,authType);
            ps.setString(offset++,transType);
            rs = ps.executeQuery();
            DeviceDetails deviceDetails = null;
            if (rs.next()) {
                deviceDetails = readDeviceDetails(rs);
            }
            return deviceDetails;
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute :\n {}", SQL_FIND_ONE);
            LOG.error(sqle);
            throw new DAOException(UAFErrorCode.DB_QUERY_DEVICE_LIST_FAILED,sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
    }

    private static final String SQL_UPDATE = "update t_devices set status=? ,updatets = ? , is_root = ? where tenant_id=? and user_name=? and device_id=? and auth_type=? and trans_type=?";
    @Override
    public void update(DeviceDetails deviceDetails) throws DAOException {
        PreparedStatement ps = null;
        try {
            int offset = 1;
            Calendar today = Calendar.getInstance();
            Timestamp currentTimestamp = new Timestamp(today.getTime().getTime());

            ps = this.conn_.prepareStatement(SQL_UPDATE);
            ps.setInt(offset++,deviceDetails.getStatus());
            ps.setTimestamp(offset++,currentTimestamp);
            ps.setString(offset++,String.valueOf(deviceDetails.isRoot()));
            ps.setString(offset++,deviceDetails.getAppID());
            ps.setString(offset++,deviceDetails.getUserName());
            ps.setString(offset++,deviceDetails.getDeviceID());
            ps.setString(offset++,deviceDetails.getAuthType());
            ps.setString(offset++,deviceDetails.getTransType());
            ps.executeUpdate();
        }catch (SQLException e){
            LOG.error("The following SQL statement failed to execute :\n {}", SQL_UPDATE);
            LOG.error(e);
        }finally {
            DBUtil.close(ps);
        }
    }

    public void insert(DeviceDetails d) throws DAOException {
    {
      String q =_SQL_INSERT;
      PreparedStatement s = null;
      try {
        int offset = 1;
        s = this.conn_.prepareStatement(q);
        s.setString(offset++, UUIDGenerator.generateID());
        s.setString(offset++, d.getUserName());
        s.setString(offset++,d.getAppID());
        s.setString(offset++,d.getAuthType());
        s.setString(offset++,d.getTransType());
        s.setString(offset++,d.getDeviceID());
        s.setString(offset++,d.getDeviceName());
        s.setString(offset++,d.getDeviceAliasName());
        s.setString(offset++,d.getDeviceType());
        s.setString(offset++,d.getOsType());
        s.setString(offset++,d.getOsVersion());
        s.setString(offset++,d.getImei());
        s.setString(offset++,d.getMac());
        s.setInt(offset++,d.getStatus());
        Calendar today = Calendar.getInstance();
        Timestamp currentTimestamp = new Timestamp(today.getTime().getTime());

        s.setTimestamp(offset++, currentTimestamp);
        s.setTimestamp(offset++, currentTimestamp);
        s.setString(offset++,String.valueOf(d.isRoot()));

        s.executeUpdate();
      } catch (SQLException exp) {
        LOG.error("The following SQL statement failed to execute :\n {}", q);
        throw new DAOException(UAFErrorCode.DB_ADD_DEVICE_FAILED,exp);
      } finally {
        DBUtil.close(s);
      }
    }
  }

    private final String _SQL_UPDATE_SQL =  "update t_devices set status=? ,updatets = ? where tenant_id=? and user_name=? and auth_type=? and trans_type=?";
    public void updateOther(String userName, String appID, String authType,String transType) throws DAOException {

    PreparedStatement s = null;
    try {
        s = this.conn_.prepareStatement(_SQL_UPDATE_SQL);
        Calendar today = Calendar.getInstance();
        Timestamp currentTimestamp = new Timestamp(today.getTime().getTime());
        s.setInt(1, Constants.AuthenticatorStatus.DELETED.getStatus());
        s.setTimestamp(2,currentTimestamp);
        s.setString(3,appID);
        s.setString(4,userName);
        s.setString(5,authType);
        s.setString(6,transType);
        s.executeUpdate();
    }catch (SQLException e){
      LOG.error("The following SQL statement failed to execute :\n {}", _SQL_UPDATE_SQL);
      LOG.error(e);
      throw new DAOException(UAFErrorCode.DB_DEL_DEVICE_FAILED,e);
    }finally {
        DBUtil.close(s);
    }
  }
}